package com.cjbdi.service;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.cjbdi.util.NumUtil;

/**
 * SfglydjazsService
 */
@Service
@Transactional
public class SfglYdjazsService{
	@Autowired  
    @PersistenceContext  
    private EntityManager entityManager;// 注入EntityManager实例 
	@Autowired
	BaseService baseService;
	//月度结案均衡指数概述（和去年同比） 
	public List getGsList(String kind,String type, String date, String fybm){ 
    	Query query = entityManager.createNativeQuery("select date,jbfy,fymc,(jan+feb+mar+apr+may+jun+jul+aug+sep+oct+nov+dec)as sj,(jan+feb+mar+apr+may+jun+jul+aug+sep+oct+nov+dec)/12 as yj ,result from sfzs_result_ydja where type='"+type+"' and date in('"+date+"','"+(Integer.parseInt(date)-1)+"') and kind='"+kind+"' and jbfy ='"+fybm+"' order by date desc");  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //附表表格
    public List getFbList(String type, String date, String fybm, String kind){
    	StringBuffer sql = new StringBuffer("select jbfy,fyjc,jan \"ja\",feb as \"f\",mar as \"m\",apr as \"a\",may as \"ma\",jun as \"j\",jul as \"ju\",aug as \"au\",sep as \"s\",oct as \"o\",nov as \"n\",dec as \"d\",result as r FROM sfzs_result_ydja,sfzs_czrk where sfzs_result_ydja.jbfy=sfzs_czrk.fybm and sfzs_czrk.year='"+date+"' and date='"+date+"' and type='"+type+"' and kind ='"+kind+"' and ");
    	if(kind.equals("高")) {
    		sql.append("jbfy not in('X00','000','ZZ0')");
    	}else {
    		sql.append("jbfy like'"+fybm.substring(0, 1)+"%'");
    	}
    	sql.append(" order by result desc");
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();   
        for(int i=0;i<rows.size();i++) {
			Map row = (Map)rows.get(i);
			row.put("id", i+1);
		}
        return rows;
    }
    //全国排名表格
    /*public List getQgTableList(String type, String date, boolean isQuanguo){ 
    	StringBuffer sql = new StringBuffer("select jbfy,fy,lastyearzs,yearzs,cz,rank() over(order by yearzs desc) as rank from(SELECT jbfy,fymc as fy,\r\n" + 
    			"sum(case when date='"+(Integer.parseInt(date)-1)+"' then result else 0 end )as lastyearzs,\r\n" + 
    			"sum(case when date='"+date+"' then result else 0 end) as yearzs,\r\n" + 
    			"(sum(case when date='"+date+"' then result else 0 end) - sum(case when date='"+(Integer.parseInt(date)-1)+"' then result else 0 end )) as cz from sfzs_result_ydja where (jbfy not in('X00','000') or jbfy is null)");
    	if(isQuanguo) {
    		sql.append(" and fymc='全国'");
    	}else {
    		sql.append(" and fymc!='全国'");
    	}
    	sql.append(" and type='"+type+"' and date in('"+date+"','"+(Integer.parseInt(date)-1)+"') and kind ='高' GROUP BY jbfy,fymc)t");
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }*/
    //中院排名表格
    /*public List getZjTableList(String type, String date, String fybm){  
    	String sql = "SELECT jbfy,fyjc as fy,\r\n" + 
        			"sum(case when date='"+(Integer.parseInt(date)-1)+"' then result else 0 end )as lastyearzs,\r\n" + 
        			"sum(case when date='"+date+"' then result else 0 end) as yearzs,\r\n" + 
        			"(sum(case when date='"+date+"' then result else 0 end) - sum(case when date='"+(Integer.parseInt(date)-1)+"' then result else 0 end )) as cz from sfzs_result_ydja,sfzs_czrk where sfzs_result_ydja.jbfy=sfzs_czrk.fybm and jbfy like'"+fybm.substring(0, 1)+"%' and type='"+type+"' and sfzs_czrk.year='"+date+"' and kind ='中' GROUP BY jbfy,fyjc ORDER BY yearzs desc";
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }*/
    //辖区排名表格
    /*public List getXqTableList(String type, String date, String fybm){  
    	String sql = "SELECT jbfy,replace(fymc,'人民法院','法院') as fy,\r\n" + 
    			"sum(case when date='"+(Integer.parseInt(date)-1)+"' then result else 0 end )as lastyearzs,\r\n" + 
    			"sum(case when date='"+date+"' then result else 0 end) as yearzs,\r\n" + 
    			"(sum(case when date='"+date+"' then result else 0 end) - sum(case when date='"+(Integer.parseInt(date)-1)+"' then result else 0 end )) as cz from sfzs_result_ydja where kind ='辖' and type ='"+type+"' and jbfy like'"+fybm.substring(0, 2)+"%' and jbfy<>'"+fybm.substring(0, 2)+"'||'0' and right(fymc,5)<>'（已撤销）' GROUP BY jbfy,fymc ORDER BY yearzs desc";
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }*/
    //附表表格（区县）
    public List getFbQxList(String type, String date, String fybm){  
    	String sql = "select replace(fymc,'人民法院','法院') as fyjc,jan as ja,feb as f,mar as m,apr as a,may as ma,jun as j,jul as ju,aug as au,sep as s,oct as o,nov as n,dec as d,result as r from sfzs_result_ydja where type='"+type+"' and sfzs_result_ydja.date ='"+date+"' and kind='辖' and jbfy like'"+fybm.substring(0, 2)+"%' and right(fymc,5)<>'（已撤销）' order by result desc";
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //附表表格（区县本身）
    public List getFbQxSelfList(String type, String date, String fybm){  
    	String sql = "select replace(fymc,'人民法院','法院') as fyjc,jan as ja,feb as f,mar as m,apr as a,may as ma,jun as j,jul as ju,aug as au,sep as s,oct as o,nov as n,dec as d,result as r from sfzs_result_ydja where type='"+type+"' and sfzs_result_ydja.date ='"+date+"' and kind='辖' and jbfy='"+fybm+"' order by result desc";
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    public void getYdjajhzsGy(Map dataMap,String kind,String type, String date, String fybm,String fymc) {//法官工作量变化指数
    	getSectionOne(dataMap,kind,type,date,fybm);
		List qgTableList = baseService.getQgTableList("sfzs_result_ydja",type, date, false, true,"");
		List qgSelfList = baseService.getQgTableList("sfzs_result_ydja",type, date, true, true,"");
		List qgTableList3 = new ArrayList();
		Map rowSelf = new HashMap();
		for(int i=qgTableList.size()-1;i>=0;i--) {//为全国报告增加
			Map row = (Map)qgTableList.get(i);
			String fy = row.get("fy").toString();
			if(fy.contains("北京")||fy.contains("天津")||fy.contains("上海")||fy.contains("重庆")) {
				continue;
			}else {
				dataMap.put("sfgl_ydjajhzs_rank1_qg",NumUtil.toSheng(fy)+"法院");
				if(fymc.equals("全国")) {
					List zjList = baseService.getZjTableList("sfzs_result_ydja",type, date, row.get("jbfy").toString(),true, "");
					Map rowzj = (Map)zjList.get(0);
					String rankZJ1 = rowzj.get("fy").toString();
					dataMap.put(33,NumUtil.toSheng(fy));
					dataMap.put("sfgl_ydjajhzs_rank1_zj", rankZJ1);//中级排名第一
				}
				break;
			}
		}
		baseService.getGyDataIntoMap(dataMap, qgTableList, qgSelfList, qgTableList3, fymc, "sfgl_ydjajhzs");
		List zjTableList = baseService.getZjTableList("sfzs_result_ydja",type, date, fybm,true, "");//当前高院所包含的中院排名列表
		List gylist = baseService.getSelfZs("sfzs_result_ydja", type, date, fybm, "");
		baseService.getZyDetailDataIntoMap(dataMap, zjTableList, gylist, fymc, "sfgl_ydjajhzs");
		//处理表格
		if(fymc.equals("全国")) {
			List fbList = getFbList(type,date,fybm,"高");
			dataMap.put("array16",qgTableList);
			dataMap.put("array50",fbList);
		}else {
			List fbList = getFbList(type,date,fybm,"中");
			dataMap.put("array31",qgTableList3);
			dataMap.put("array32",zjTableList);
			dataMap.put("array51",fbList);
		}
		
    } 
    public void getYdjajhzsZy(Map dataMap,String kind,String type, String date, String fybm,String fymc) {//裁判文书稳定指数
    	
    	List fbList = getFbQxList(type,date,fybm);
    	baseService.handleFb(fbList, fymc);
		List zjTableList = new ArrayList();
		List tdTableList = new ArrayList();
		List xqTableList = baseService.getXqTableList("sfzs_result_ydja",type, date, fybm,true,"");
		List zylist = baseService.getSelfZs("sfzs_result_ydja", type, date, fybm, "");
		baseService.getQxDetailDataIntoMap(dataMap, xqTableList, zylist, fymc, "sfgl_ydjajhzs");
		
		List tsTableList = baseService.getTsTableList("sfzs_result_ydja",type, date, fybm,true,"");
    	baseService.getTsDataIntoMap(dataMap, tsTableList, fybm, "sfgl_ydjajhzs");
		if(fybm.startsWith("1")||fybm.startsWith("2")||fybm.startsWith("M")||fybm.startsWith("9")) {
			getSectionOne(dataMap,"辖",type,date,fybm);
			//同等直辖市中院排名表格		
			tdTableList = baseService.getTdZxTableList("sfzs_result_ydja",type, date, fybm,true, "");
			baseService.getTdZxDataIntoMap(dataMap, tdTableList, fybm, "sfgl_ydjajhzs");
			zjTableList = baseService.getZjZxTableList("sfzs_result_ydja",type, date, fybm,true, "");
			baseService.getZyDataIntoMap(dataMap, zjTableList, fybm, "sfgl_ydjajhzs");
			//处理表格
			dataMap.put("array52",zjTableList);
			dataMap.put("array53",tdTableList);
			dataMap.put("array54",xqTableList);
			dataMap.put("array55",tsTableList);
			dataMap.put("array75",fbList); 
		}else {
			getSectionOne(dataMap,kind,type,date,fybm);
			//同等规模城市排行
			List infoList = baseService.getQxInfo("sfzs_result_ydja",type, date, fybm,true, "");
			tdTableList = baseService.getTdZjTableList("sfzs_result_ydja",type, date, fybm, true,""); 
			baseService.getTdCityDataIntoMap(dataMap, infoList, tdTableList, "sfgl_ydjajhzs", fybm);
			
			zjTableList = baseService.getZjTableList("sfzs_result_ydja",type, date, fybm,true, "");
			baseService.getZyDataIntoMap(dataMap, zjTableList, fybm, "sfgl_ydjajhzs");
			//同等收案量排行
			List tdSalTableList = baseService.getTdSalTableList("sfzs_result_ydja",type, date, fybm,true, "");
			baseService.getTdSalDataIntoMap(dataMap, tdSalTableList, fymc,"sfgl_ydjajhzs");

			//处理表格
			dataMap.put("array56",zjTableList);
			dataMap.put("array57",tdTableList);
			dataMap.put("array58",xqTableList);
			dataMap.put("array59",tsTableList);
			dataMap.put("array80",fbList); 
		}
		
		
		
		
    }
    
    public void getQxZs(Map dataMap,String kind, String type, String date, String fybm) {//区县法院指数
    	getSectionOne(dataMap,kind,type,date,fybm);
    	List xqTableList = baseService.getXqTableList("sfzs_result_ydja",type, date, fybm,true,"");
    	List gylist = baseService.getGyOrZyXqZs("sfzs_result_ydja", type, date, fybm, "","高");
    	List ylist = baseService.getGyOrZyXqZs("sfzs_result_ydja", type, date, fybm, "","中");
    	baseService.getQxDataIntoMap(dataMap, xqTableList, fybm, "sfgl_ydjajhzs",gylist,ylist);
    	String ssxq_gy_rank = baseService.getRankInGyxq("sfzs_result_ydja",type,date,fybm,true,"").get(0).toString();
    	dataMap.put("sfgl_ydjajhzs_rank_gy",  "第" + NumUtil.toChineseNum(ssxq_gy_rank) + "位");
    	List tsTableList = baseService.getTsTableList("sfzs_result_ydja",type, date, fybm,true,"");
    	baseService.getTsDataIntoMap(dataMap, tsTableList, fybm, "sfgl_ydjajhzs");
    	List fbList = getFbQxSelfList(type,date,fybm);
		for(int i=0;i<fbList.size();i++) {
			Map row = (Map)fbList.get(i);
			row.put("id", i+1);
			row.put("lv", NumUtil.toNxiaoshu(row.get("r").toString(), 2)+"%");
		}
    	dataMap.put("array26",xqTableList);
    	dataMap.put("array27",tsTableList);
    	dataMap.put("array44",fbList); 
    }
    
    public void getSectionOne(Map dataMap,String kind,String type, String date, String fybm) {
    	List gslist = getGsList(kind,type,date,fybm);
    	Map mapYear = new HashMap();
    	Map mapLastYear = new HashMap();
    	int sj = 0;//结案量
    	int yj = 0;//月结案量
    	
    	int syLastYear = 0;
    	int yjLastYear = 0;    	
    		
    	double zs = 0;
    	double zsLastYear = 0;
    		
    	
    	if(gslist.size()>1) {
    		mapYear = (Map)gslist.get(0);
        	mapLastYear = (Map)gslist.get(1);
        	sj = Integer.parseInt(mapYear.get("sj").toString());//结案量
        	yj = Integer.parseInt(mapYear.get("yj").toString());
        	syLastYear = Integer.parseInt(mapLastYear.get("sj").toString());
        	yjLastYear = Integer.parseInt(mapLastYear.get("yj").toString());    	
        	zs = NumUtil.toNxiaoshu(mapYear.get("result").toString(), 2);
        	zsLastYear = NumUtil.toNxiaoshu(mapLastYear.get("result").toString(), 2);
        	
    	}
    	
    	
    	dataMap.put("sfgl_ydjajhzs", zs);
		dataMap.put("sfgl_ydjajhzs_lastyear", zsLastYear);
		
		dataMap.put("sfgl_ydjajhzs_sj", NumUtil.toWan(sj));
		dataMap.put("sfgl_ydjajhzs_sj_lastyear", NumUtil.toWan(syLastYear));
		
		dataMap.put("sfgl_ydjajhzs_yj", NumUtil.toWan(yj));
		dataMap.put("sfgl_ydjajhzs_yj_lastyear", NumUtil.toWan(yjLastYear));
		
		if(zs>zsLastYear) {
			dataMap.put("sfgl_ydjajhzs_tb", "上升"+(NumUtil.toNxiaoshu(zs-zsLastYear,2)));//同比
		}else if(zs<zsLastYear) {
			dataMap.put("sfgl_ydjajhzs_tb", "下降"+(NumUtil.toNxiaoshu(zsLastYear-zs,2)));//同比
		}else {
			dataMap.put("sfgl_ydjajhzs_tb", "不变");//同比
		}
		if(kind.equals("中")) {
			Map zyxq = new HashMap();
			zyxq.put("fy", dataMap.get("fy")+"法院");
			zyxq.put("lastyearzs",zs);
			zyxq.put("yearzs",zsLastYear);
			zyxq.put("cz",NumUtil.toNxiaoshu(zs-zsLastYear,2));
			dataMap.put("zyxq", zyxq);
		}
    }

}
